EXPLAIN EXTENDED

How to create fast database queries

Adjacency list vs. nested sets: MySQL

with 15 comments

Continuing the series:

What is better to store hierarchical data: nested sets model or adjacency list (parent-child) model?

For detailed explanations of the terms, see the first article in the series:

This is the last article of the series which covers MySQL.

MySQL differs from the other systems, since it is the only system of the big four that does not support recursion natively. It has neither recursive CTE's nor CONNECT BY clause, not even rowset returning functions that help to emulate recursion in PostgreSQL 8.3.

MySQL supports a thing that all other systems either lack or implement inefficiently: session variables. They can be set in a SELECT clause and can be used to keep some kind of a state between the rows as they are processed and returned in a rowset.

This of course is against the whole spirit of SQL, since SQL implies operations on whole sets and session variables operate on rows and are totally dependent on the order they are returned or processed. But if used properly, this behavior can be exploited to emulate some things that MySQL lacks: analytic functions, efficient random row sampling etc.

Hierarchical functions are among the things that need to be emulated in MySQL using session variables to keep the function state.

Here's the old article in my blog that shows how to do this:

On the other hand, MySQL implements one more thing that is useful for nested sets model: SPATIAL indexes.

Spatial indexes and nested sets model

Unlike most other systems, MySQL allows creating indexes of different types, one of them being SPATIAL.

A spatial index is an R-Tree structure that allows indexing multidimensional values (though MySQL only indexes two-dimensional ones). Each value is represented by its minimal bounding box (minimal rectangle that contains the whole shape), and this is what is stored in the index.

The index allows to find an answer efficiently to the following question: given a point, what are the boxes that contain it?

Though this type of query is commonly used on geometrical or geographical data, like find all public toilets within 100 meters of my current location and for God's sake don't make it a fullscan. However, this index can be used on any type of query that requires searching a given point in a range defined by two columns just as well.

A plain B-Tree index is good for range queries: find all values within the range defined by two boundaries, like shown on this picture:

Index

Black dots show values that fall inside the range (defined with two constant boundaries). It's easy to find these values if they are sorted: all values will make a contiguous block. That's what exactly the B-Tree index do: sort the values and return contiguous blocks.

But if we need to find variable ranges containing a constant point, like on this picture:

Range

, a B-Tree index won't help, since we have two values here which we just cannot sort using one sort order. An R-Tree index, on the contrary, is perfect for this type of query.

Tasks requiring seaching for a value inside a known range are very common, that's why almost all database management systems provide a way to build and use a B-Tree index.

There is also a certain class of tasks that require searching for all ranges containing a known value:

and several others. These tasks can be improved by using R-Tree capabilities of MySQL:

The nested sets model belongs to both classes of tasks. It requires the first query (find variable values within a constant range) to build the list of descendants, and the second query (find variable ranges that contain a constant value) to build the list of ancestors.

That's exactly why it's so fast on building the list of descendants and so slow on building the list of ancestors.

Using an R-Tree index, nested sets model can be improved.

MySQL can only create an R-Tree index on a GEOMETRY type in a MyISAM table and use it in two special predicates, MBRContains and MBRWithin.

We should represent out nested sets boundaries (lft, rgt) as a LineString(Point(-1, lft), Point(1, rgt)), and search for a Point(0, value) using any of the predicates above.

This is not much of a stretch, actually, from the logical point of view: the nested sets are usually graphically represented as big boxes (parents) containing smaller boxes (children), and that's exactly what these predicates are designed for: searching for a boxes containing other boxes.

Let's create a sample table:

Table creation script

The table contains 8 levels of hierarchy, 5 children to each parent and 2,441,405 records. Hierarchical attributes are defined for both adjacency list model (parent) and nested sets model (lft, rgt). All these fields are indexed with plain B-Tree indexes.

The field sets represents the diagonal of the bounding box of each record. All children's boxes are contained within the parent box. This field is indexed with an R-Tree (SPATIAL) index.

To query the adjacency list, we also need to create a pair of special functions as described in the article about hierarchical queries in MySQL:

The hierarchical functions

These functions are described in more detail in this and this article.

Now, let's run the queries.

All descendants

Nested sets

1
2
3
4
5
SELECT  SUM(LENGTH(hc.stuffing))
FROM    t_hierarchy hp
JOIN    t_hierarchy hc
ON      hc.lft BETWEEN hp.lft AND hp.rgt
WHERE   hp.id = 42

We don't use any spatial columns or indexes here.

Since this query requiries searching for all records with values of lft lying within the given range (that between lft and rgt of the parent record), a plain B-Tree index on a plain INT column is just fine.

View query details

The query completes in 300 ms.

Adjacency list

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT  SUM(LENGTH(stuffing))
FROM    (
        SELECT  42 AS id
        UNION ALL
        SELECT  hierarchy_connect_by_parent_eq_prior_id(id) AS id
        FROM    (
                SELECT  @start_with := 42,
                        @id := @start_with,
                        @level := 0
                ) vars, t_hierarchy
        WHERE   @id IS NOT NULL
        ) ho
JOIN    t_hierarchy hi
ON      hi.id = ho.id

Since MySQL does not support recursive queries natively, we use the function to iterate the trees and a set of session variables to maintain the state of the function between calls.

To provide the results as a resultset, we call the function it SELECT clause of a query over the table, disregarding the input parameters. The table in the FROM clause is used as a dummy rowsource.

View query details

This query runs for 7 seconds.

All ancestors

Nested sets

1
2
3
4
5
6
7
SELECT  hp.id, hp.parent, hp.lft, hp.rgt, hp.data
FROM    t_hierarchy hc
JOIN    t_hierarchy hp
ON      MBRWithin(Point(0, hc.lft), hp.sets)
WHERE   hc.id = 1000000
ORDER BY
        lft

This query uses the R-Tree index. To do that, we convert lft to a point with coordinates (0, lft) and search for all boxes containing this point using MBRWithin.

View query details

The query completes in 15 ms. This result is by far faster than everything we saw before. The same queries issued by the other systems are not assisted or poorly assisted by B-Tree indexes, and usually this query is a matter of seconds.

Adjacency list

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT  hp.id, hp.parent, hp.lft, hp.rgt, hp.data
FROM    (
        SELECT  @r AS _id,
                @level := @level + 1 AS level,
                (
                SELECT  @r := NULLIF(parent, 0)
                FROM    t_hierarchy hn
                WHERE   id = _id
                )
        FROM    (
                SELECT  @r := 1000000,
                        @level := 0
                ) vars,
                t_hierarchy hc
        WHERE   @r IS NOT NULL
        ) hc
JOIN    t_hierarchy hp
ON      hp.id = hc._id
ORDER BY
        level DESC

Not that we don't even use a function for this query.

Each record has only one parent, and id is a PRIMARY KEY, thus the ancestry chain can be represented as a linked list.

To iterate the linked list, we can use an approach describe in this article:

, which requires no function, just a correlated subquery.

View query details

This query completes in 600 ms, which is much longer than the nested sets solution.

All descendants up to a certain level

Nested sets

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT  hc.id, hc.parent, hc.lft, hc.rgt, hc.data
FROM    t_hierarchy hp
JOIN    t_hierarchy hc
ON      hc.lft BETWEEN hp.lft AND hp.rgt
JOIN    t_hierarchy hr
ON      MBRWithin(Point(0, hc.lft), hr.sets)
WHERE   hp.id = ?
GROUP BY
        hc.id
HAVING  COUNT(*) <=
        (
        SELECT  COUNT(*)
        FROM    t_hierarchy hp
        JOIN    t_hierarchy hrp
        ON      MBRWithin(Point(0, hp.lft), hrp.sets)
        WHERE   hp.id = ?
        ) + 2
ORDER BY
        hc.lft

This query is adjusted for better usage of the R-Tree indexes.

We find all descendents of the item in question and then calculate the total number of parents (which gives us the depth level of each of the children). Then we just compare it with the level of the item.

This solution depends on the number of item's children too, however, let's see the performance:

View query for item 42

View query for item 42

The query for item 42 (which has about 20,000 descendants) took minutes in the other systems. Now it completes in less than 5 seconds.

The same query for item 31,415 is over in just 10 ms.

Adjacency list

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT  hi.id, hi.parent, hi.lft, hi.rgt, hi.data
FROM    (
        SELECT  ? AS id
        UNION ALL
        SELECT  hierarchy_connect_by_parent_eq_prior_id_with_level(id, 2) AS id
        FROM    (
                SELECT  @start_with := ?,
                        @id := @start_with,
                        @level := 0
                ) vars, t_hierarchy
        WHERE   @id IS NOT NULL
        ) ho
JOIN    t_hierarchy hi
ON      hi.id = ho.id

This query imitates recursion, so performance does not directly depend on the number of descendants.

View query for item 42

View query for item 31,415

Both queries complete in 600 ms

Summary

MySQL differs from the other systems in its possibilities to handle hierarchical data.

On one hand, it lacks a native way to do recursive queries which makes traversing the hierarchy trees harder. It can be emulated using a custom function and session variables to maintain the recursion stack, but this solution is more slow.

On the other hand, MySQL supports R-Tree indexes which can be used to query the ranges containing a given value. This type of search is required for the nested sets queries and R-Tree index is faster.

However, adjacency list is still faster for retrieving all descendants up to the given level.

Both adjacency lists and nested sets require extra maintenance in MySQL: adjacency lists require building a custom function to query each table, nested sets require a function to update it.

Updating a nested sets model can be slow too since R-Tree indexes take much longer time to add to them than B-Tree indexes.

However, using R-Tree indexes, nested sets model is extra fast for searching for all descendants and all ancestors, and shows decent performance in determining the item's levels and filtering on them.

In MySQL, it is advisable to add the level column into the nested sets model which will make it super fast for all three types of queries. However, this will make it even more harder to update.

It should also be noted that the only storage engine that allows R-Tree indexes is MyISAM. In case of an update (which can affect millions of rows even to insert a single record), all table will be locked and will not be able to be queried.

Conclusion

In MySQL, the nested sets model should be preferred if the updates to the hierarhical structure are infrequent and it is affordable to lock the table for the duration of an update (which can take minutes on a long table).

This implies creating the table using MyISAM storage engine, creating the bounding box of a GEOMETRY type as described above, indexing it with a SPATIAL index and persisting the level in the table.

If the updates to the table are frequent or it is inaffordable to lock the table for a long period of time implied by an update, then the adjacency list model should be used to store the hierarchical data.

This requires creating a function to query the table.

MySQL is the only system of the big four (MySQL, Oracle, SQL Server, PostgreSQL) for which the nested sets model shows decent performance and can be considered to stored hierarchical data.

Written by Quassnoi

September 29th, 2009 at 11:00 pm

Posted in MySQL

15 Responses to 'Adjacency list vs. nested sets: MySQL'

Subscribe to comments with RSS

  1. “find all public toilets within 100 meters of my current location and for God’s sake don’t make it a fullscan”

    that made me laugh out loud this morning – thank you :)

    Gene

    15 Oct 12 at 14:09

  2. Ooh, thanks for the tip about using an R-tree index to speed up the nested set ancestor queries.

    Annie

    13 Jan 13 at 11:23

  3. Other databases have spatial indexes as well… wouldn’t they be able to use the nested sets just as effectively as MySQL, or is there something magical about MyISAM’s R-Tree indexing?

    Christopher Smith

    16 Jan 13 at 23:06

  4. @ChristopherSmith: of course they would. SQL Server and PostgreSQL, however, don’t implement their spatial indexes as R-Tree.

    Quassnoi

    17 Jan 13 at 02:29

  5. Interesting.

    But MyISAM doesn’t support transactions, so what’s the point of the transaction commands?

    Peter Brawley

    9 Aug 13 at 01:32

  6. @Peter: I just copy and paste those scripts from post to post, changing them as needed.

    Quassnoi

    9 Aug 13 at 13:12

  7. Thanks for sharing your scripts and test results. Very useful! Would be interesting to see the numbers for InnoDB.

    Daniel

    23 Oct 13 at 17:35

  8. Thank you so much, for applied knowledgeable examples how to implement MySQL spatial data type toward hierarchical model.
    WEB is full of posts recycling same examples with “electronics sht” without unveiling applied basics of how properly define and build these indexes in MySQL properly and thus taking model’s usage to its full potential strength.
    Finally, here, straight and forward post how to translate integer position index to it’s geometrical interpretation required by a model.

    Thanks so much again!

    Misha Dar

    4 Nov 14 at 23:29

  9. This is a great series of articles, thank you. It was a pleasure to read them even as just a hobbyist.

    But I’ve found that MyISAM doesn’t support foreign keys. So (if I’m not mistaken) I have to make a choice: either I have InnoDB tables and foreign keys or MyISAM tables and no foreign keys. Now, I have a hierarchy with only about 100 elements (and it won’t ever go over 200), so lookup is very fast even without a spatial index. If I’d like to keep foreign keys for integrity, may I opt to not use spatial indexes or are there any other reasons for using them besides lookup speed?

    Péter Wolf

    8 Jan 16 at 22:25

  10. @Quassnoi, I’m sorry I didn’t notice your reply until now.

    PostgreSQL absolutely does R-Tree indexes: https://www.postgresql.org/docs/8.1/static/indexes-types.html

    Christopher Smith

    24 Apr 17 at 20:36

  11. @Christopher: I remember 2006, it was a nice year: I got married, found a good job, took alpine skiing. Unfortunately, some sad stuff also happened that year: my brother-in-law divorced, my parents’ cat died and PostgreSQL dropped R-Tree support.

    Quassnoi

    24 Apr 17 at 20:57

  12. @Quassnoi R-Tree’s were migrated over to the GiST subsystem, and they’re still there as SP-GiST. It’s nice because SP-GiST supports a more flexible set of spatial indexing techniques.

    Christopher Smith

    24 Apr 17 at 21:32

  13. @Christopher: of course, and that’s why I wrote PostgreSQL did not implement its spatial indexes using R-Tree. PostgreSQL does implement spatial indexing of course, it just does not do it using R-Tree.

    Quassnoi

    24 Apr 17 at 21:43

  14. MySQL 8.0 (and MariaDB 10.2) do have Recursive CTEs.

    Rick James

    20 Jul 19 at 03:29

  15. From what I can see in the MySQL docs, both MyISAM *and* InnoDB now supports SPATIAL and R-tree indexes.

    Yan Wong

    9 May 21 at 18:16

Leave a Reply